<?php

class SimpleSQLiteDatabase implements Whatsit_Database
{
	private $username;
	protected $db;
	private $userid;
	
    function __construct($username)
    {
    	$this->username = $username;
    	try {
    		$this->db = new PDO('sqlite:../database/whatsits.sqlite');
    	} catch (PDOException $e) {
    		error_log('Couldnt connect to database' . $e->getMessage());
    		die($e->getMessage());
    	}
    }
    
    function __destruct()
    {
    	$this->db = NULL;
    }

	private function addPrefs(&$whatsit_obj)
	{
		$id = $whatsit_obj->getID();
		$sql = "SELECT * FROM preference WHERE instance_id = $id;";
		$result = $this->db->query($sql);
		foreach ($result as $entry) {
			$whatsit_obj->setPref($entry['pref_name'], $entry['pref_value']);
		}	
	}
	
	private function getPref($instance_id, $pref_name)
	{
		$select = "SELECT pref_value FROM preference, display_instance, user ";
		$select .= "WHERE preference.instance_id = $instance_id ";
		$select .= "	AND preference.pref_name = '$pref_name' ";
		$select .= "	AND preference.instance_id = display_instance.instance_id ";
		$select .= "	AND display_instance.user_id = user.user_id AND user.user_name = '{$this->username}' ";
		$select .= "LIMIT 0,1;";
		$results = $this->db->query($select);
		if ($results->rowCount() == 1) {
			$row = $results->fetch();
			return $row['pref_value'];
		} else {
			return false;
		}
	}

    /**
    * gets an array of whatsits for a particular user ane pane
    * @param string $user_id id of the user
    * @param string $pane_name the name of the pane.  could be "right" or "left" or "center" depending on your system's configuration
    * @returns an array of urls of whatsits in the pane from top to bottom in order
    */
    public function getWhatsits($pane_name)
    {
    	$whatsit_array = array();
    	$sql = "SELECT whatsit.*, strftime('%s',whatsit.cache_ts) AS cache_create_time, display_instance.position, display_instance.instance_id ";
    	$sql .= "FROM whatsit, display_instance, pane, user ";
    	$sql .= "WHERE display_instance.whatsit_id = whatsit.whatsit_id ";
    	$sql .= "	AND display_instance.pane_id = pane.pane_id ";
    	$sql .= "	AND display_instance.user_id = user.user_id ";
    	$sql .= "	AND user.user_name = '{$this->username}' ";
    	$sql .= "	AND pane.pane_name = '$pane_name' ";
    	$sql .= "ORDER BY display_instance.position DESC;";
    	$result = $this->db->query($sql);
    	foreach ($result as $entry) {
    		$whatsit = new Whatsit((int)$entry['instance_id'], $entry['url']);
    		if (isset($entry['cache']) && strlen($entry['cache']) > 10) {
    			if (time() > ((int)$entry['cache_create_time'] + (int)$entry['cache_life'])) {
    				$whatsit->setXMLText($entry['cache']);
    			} else {
    				//insert code here for refreshing cache	
    			}
    		}
    		if (isset($entry['title'])) {
    			$whatsit->setTitle($entry['title']);
    		}
    		$this->addPrefs($whatsit);
    		$whatsit_array[] = $whatsit;
    	}
    	return $whatsit_array;
    }
    
    public function setPref($id, $pref_name, $pref_value)
    {
    	if ($this->getPref($id, $pref_name) !== false) {
    		$sql = "UPDATE preference SET pref_value = '$pref_value' WHERE instance_id = $id AND pref_name = '$pref_name';";
    	} else {
    		$sql = "INSERT INTO preference (instance_id, pref_name, pref_value) VALUES ($id, '$pref_name', '$pref_value');";
    	}
    	$res = $this->db->exec($sql);
    	if ($res > 0) return true;  else return false;
    }
    
    private function getPaneId($pane_name)
    {
    	$sql = "SELECT pane_id FROM pane WHERE pane_name = '$pane' LIMIT 0,1;";
    	$result = $this->db->query($sql);
    	if ($result && count($result) == 1) {
    		$row = $result->fetch();
    		return (int)$row['pane_id'];
    	} else {
    		return false;
    	}
    }
    
    private function getUserId()
    {
    	if (isset($this->userid)) return $this->userid;
    	$sql = "SELECT user_id FROM user WHERE user_name = '{$this->username}' LIMIT 0,1;";
    	$result = $this->db->query($sql);
    	if ($result && $result->rowCount() == 1) {
    		$row = $result->fetch();
    		return $this->userid = (int)$row['user_id'];
    	} else {
    		return false;
    	}
    }

    public function addWhatsit($url, $pane)
    {
    	$select = "SELECT * FROM whatsit WHERE url = '$url' LIMIT 0,1;";
    	$result = $this->db->query($select);
    	if ($result && $result->rowCount() > 0) {
    		$row = $result->fetch();
    		$id = (int)$row['whatsit_id'];
    	} else {
    		$insert = "INSERT INTO whatsit (url) VALUES ('$url');";
    		$result = $this->db->exec($insert);
    		$id = $this->db->lastInsertId();
    	}
    	$pane_id = $this->getPaneId($pane);
    	$user_id = $this->getUserId();
		$insert = "INSERT INTO display_instance (whatsit_id, user_id, pane_id, position) ";
		$insert .= "VALUES ($id, $user_id, $pane_id, 0);";
		$this->db->exec($insert);
    	return $this->db->lastInsertId();
    }

	public function moveWhatsit($whatsit_id, $from_pane, $to_pane, $after_whatsit_id)
	{
		$to_pane_id = $this->getPaneId($to_pane);
		$user_id = $this->getUserId();
		$sqlselect = "SELECT position FROM display_instance ";
		$sqlwhere = "WHERE whatsit_id = $after_whatsit_id ";
		$sqlconstraints = "AND user_id = $user_id AND pane_id = $to_pane_id ";
		$sqllimit = "LIMIT 0,1;";
		$result = $this->db->query($sqlselect.$sqlwhere.$sqlconstraints.$sqllimit);
		$row = $result->fetch();
		$position_top_value = $row['position'];
		$sqlwhere = "WHERE position < $position_top_value ";
		$sqllimit = "ORDER BY position DESC LIMIT 0,1";
		$result = $this->db->query($sqlselect.$sqlwhere.$sqlconstraints.$sqllimit);
		$row = $result->fetch();
		$position_bottom_value = $row['position'];
		$new_position_value = ($position_top_value + $position_bottom_value)/2;
		$update = "UPDATE display_instance SET position = $new_position_value WHERE instance_id = $whatsit_id;";
		$this->db->exec($update);
	}
	
	public function getWhatsit($id)
	{
		$sql = "SELECT whatsit.*, strftime('%s',whatsit.cache_ts) AS cache_create_time, display_instance.instance_id ";
    	$sql .= "FROM whatsit, display_instance, pane, user ";
    	$sql .= "WHERE display_instance.whatsit_id = whatsit.whatsit_id ";
    	$sql .= "	AND display_instance.pane_id = pane.pane_id ";
    	$sql .= "	AND display_instance.user_id = user.user_id ";
    	$sql .= "	AND user.user_name = '{$this->username}' ";
    	$sql .= "	AND display_instance.instance_id = $id ";
    	$sql .= "LIMIT 0,1;";
    	try {
    		$result = $this->db->query($sql);
    	} catch (PDOException $e) {
    		die ($e->getMessage());	
    	}
    	$entry = $result->fetch();
    	$whatsit = new Whatsit((int)$entry['instance_id'], $entry['url']);
    	if (isset($entry['cache']) && strlen($entry['cache']) > 10) {
    		if (time() > ((int)$entry['cache_create_time'] + (int)$entry['cache_life'])) {
    			$whatsit->setXMLText($entry['cache']);
    		} else {
   				//insert code here for refreshing cache	
    		}
    	}
    	if (isset($entry['title'])) {
    		$whatsit->setTitle($entry['title']);
    	}
    	$this->addPrefs($whatsit);
    	return $whatsit;
	}
}